/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import action.administration.BidItem;
import action.administration.MarkovItem;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.MDPModel;

/**
 *
 * @author Trang
 */
public class BidhistoryDAO {
    private Connection con;

    public BidhistoryDAO() {
        con = DBFactory.createConnection("onlineshopping");
    }
    
    public boolean addBid(BidItem bid) {
        boolean result = false;

        try {         
  
            String sqlst1 = "INSERT INTO bidhistory"
                    + "(keyword, createDate, "
                    + "qualityScore, cpc,impressions,"
                    + " clicks, ctr, avgCpc, cost)"
                    + "VALUE(?, ?, ?, ?, ?, ?, ?, ?, ?)";

            PreparedStatement ps = con.prepareStatement(sqlst1);
            ps.setString(1, bid.getKeyword());
            ps.setDate(2, new java.sql.Date (bid.getDate().getTime()));
            ps.setInt(3, bid.getQualityScore());
            ps.setDouble(4,bid.getCpc());
            ps.setInt(5, bid.getImpressions());
            ps.setInt(6, bid.getClicks());
            ps.setDouble(7, bid.getCtr());
            ps.setDouble(8, bid.getAvgCpc());
            ps.setDouble(9, bid.getCost());
            
            ps.executeUpdate();
            
            //get new member account id
            
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(BidhistoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        System.out.println(result);
        return result;
    }
    public List<MDPModel> getAllModel()
    {
        List<MDPModel> result = new ArrayList<MDPModel>();
        try
        {

            String sql = "SELECT  * "
                    + "FROM keyword2 ORDER BY totalClick DESC";
            
            PreparedStatement ps = con.prepareStatement(sql);
            
            ResultSet rs = ps.executeQuery();
            
            while(rs.next())
            {
                String keyword = rs.getString("keyword");
                int totalImpresstion = rs.getInt("totalImpresstion");
                int totalClick = rs.getInt("totalClick");
                double Pxab = rs.getDouble("Pxab");
                
                MDPModel model = new MDPModel(keyword, totalImpresstion, totalClick, Pxab);
                
                result.add(model);

            }
        }
        catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
    public boolean addMarkov(MarkovItem mitem) {
        boolean result = false;

        try {         
  
            String sqlst1 = "INSERT INTO markovitem"
                    + " (ketword, optimalPolicy) "
                    + "VALUE(?, ?)";
            System.out.println(sqlst1);
            PreparedStatement ps = con.prepareStatement(sqlst1);
            ps.setString(1, mitem.getKeyword()); 
            ps.setDouble(2, mitem.getOptimalPolicy());          
            ps.executeUpdate();
            
            //get new member account id
            
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(BidhistoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        System.out.println("Result" +result);
        return result;
    }
    public List<MarkovItem> getMarkovItem(int limit)
    {
        List<MarkovItem> result = new ArrayList<MarkovItem>();
        try
        {

            String sql = "SELECT  * "
                    + "FROM markovitem ORDER BY optimalPolicy DESC LIMIT ? ";
            
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, limit);
            ResultSet rs = ps.executeQuery();
            
            while(rs.next())
            {
                String keyword = rs.getString("keyword");
                double optimalPolicy = rs.getDouble("optimalPolicy");
                
                MarkovItem model = new MarkovItem(keyword, optimalPolicy);
                
                result.add(model);

            }
        }
        catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
    public boolean DeleteBidHistoty() {
          boolean result = false;
        try {
            String sqlst = "DELETE FROM bidhistory WHERE idhistory > 1";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.executeUpdate();
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
}
